
/****** Object:  StoredProcedure [dbo].[SFDA_attachment_auditFetch]    Script Date: 07/27/2013 18:39:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE proc [dbo].[SFDA_attachment_auditFetch]
@applicationType nvarchar(50)='',
@subID nvarchar(10)='',
@workStation nvarchar(50)='',
@operator nvarchar(50)='',
@auditorType nvarchar(50)=''
as
set nocount on
if OBJECT_ID('tmpdb_#tmp1') is not null drop table #tmp1
create table #tmp1(pk int,image_filename nvarchar(200),title nvarchar(200),
	image image,image_date_created datetime,itemType nvarchar(50),correspondingNo nvarchar(50),
	workStation nvarchar(50),operator nvarchar(50),auditorType nvarchar(50))

insert into #tmp1(pk,image_filename,title,image,image_date_created,itemType,correspondingNo,workStation,operator,auditorType)
select pk,image_filename,title,image,image_date_created,itemType,correspondingNo,workStation,operator,auditorType
from T_attachmentInfoUse where  correspondingNo = @subID --and workStation = @workStation and operator = @operator and auditorType = @auditorType

declare @applicationSubID int,@relateApplicationNo nvarchar(50)
select @relateApplicationNo = relateApplicationNo from T_applicationFormHead 
where subID = @subID and active = 1


select @applicationSubID = subID from T_applicationFormHead 
where applicationFormNo = @relateApplicationNo and active = 1
--select @applicationSubID

insert into #tmp1(pk,image_filename,title,image,image_date_created,itemType,correspondingNo,workStation,operator,auditorType)
select pk,image_filename,title,image,image_date_created,itemType,correspondingNo,workStation,operator,auditorType
from T_attachmentInfoUse where  correspondingNo = @applicationSubID and workStation = @workStation and operator = @operator and auditorType = @auditorType

update #tmp1 set operator = b.staffName from #tmp1 a inner join T_staffFiles b on a.operator = b.userName

select * from #tmp1 order by image_date_created




GO


